package org.azmat.demojpa.SQL.JDBC.Repository;

import org.azmat.demojpa.SQL.JDBC.Entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 添加学生
    public int addStudent(Student student) {
        return jdbcTemplate.update("insert into student(studentname, gender, age) values(?, ?, ?)", student.getStudentname(), student.getGender(), student.getAge());
    }

    // 删除学生
    public int deleteStudent(int id) {
        return jdbcTemplate.update("delete from student where id = ?", id);
    }

    // 修改学生
    public int updateStudent(Student student) {
        return jdbcTemplate.update("update student set studentname = ?, gender = ?, age = ? where id = ?", student.getStudentname(), student.getGender(), student.getAge(), student.getId());
    }

    // 查询所有学生
    public List<Student> getAllStudents() {
        return jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));
    }

    // 查询id查询学生
    public Student getStudentById(int id) {
        return jdbcTemplate.queryForObject("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper<>(Student.class));
    }

    // 查询所有学生，支持分页
    public Page<Student> findAll(Pageable pageable) {
        String sql = "select * from student";
        if (pageable.getSort().isSorted()) {
            sql += " order by " + pageable.getSort().iterator().next().getProperty() + " " + (pageable.getSort().iterator().next().isAscending() ? "asc" : "desc");
        }
        int start = pageable.getPageNumber() * pageable.getPageSize();
        int size = pageable.getPageSize();
        List<Student> students = jdbcTemplate.query(sql + " limit ?, ?", new Object[]{start, size}, new BeanPropertyRowMapper<>(Student.class));
        return new PageImpl<>(students, pageable, getCount());
    }

    // 获取总记录数
    private int getCount() {
        return jdbcTemplate.queryForObject("select count(*) from student", Integer.class);
    }
}
